package org.ww.jd.snatch;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;

public class SnatchRecordDAO {
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
		}
	}

	public static void main(String[] args) throws SQLException {
		SnatchRecordDAO dao = new SnatchRecordDAO();
		List<HisDealDetail> res = dao.excuteQuery("");
		System.out.println(res);
	}

	private QueryRunner runner = new QueryRunner();

	private Connection createConnection() throws SQLException {

		String url = "jdbc:mysql://localhost:3306/snatchjd?user=root&password=root";
		Connection conn = DriverManager.getConnection(url);
		return conn;
	}

	public List<HisDealDetail> excuteQuery(String where) throws SQLException {
		String sql = "select * from his_dealdetail";
		if (where != null && !where.isEmpty()) {
			sql = sql + " where " + where;
		}
		ResultSetHandler<List<HisDealDetail>> rsh = new BeanListHandler<HisDealDetail>(
				HisDealDetail.class);
		try (Connection conn = createConnection()) {
			return runner.query(conn, sql, rsh);
		}
	}

	public void insertWaiting(String goodsid) throws SQLException {
		String insert_sql = "insert into waiting_id(goodsid,ts) values (?,?)";
		try (Connection conn = createConnection()) {
			runner.update(conn, insert_sql, goodsid,
					new Timestamp(System.currentTimeMillis()));
		}
	}

	public List<String> queryWaitingIds() throws SQLException {
		String sql = "select goodsid from waiting_id";
		ResultSetHandler<List<String>> rsh = new ColumnListHandler<String>();
		try (Connection conn = createConnection()) {
			return runner.query(conn, sql, rsh);
		}
	}

	public void insertDetail(HisDealDetail detail) throws SQLException {
		String insert_sql = "insert into his_dealdetail(goodsid,goodsname,jdprice,status,place,dealprice,dealuser,starttime,dealdatetime,ts) values (?,?,?,?,?,?,?,?,?,?)";
		String delete_sql = "delete from waiting_id where goodsid = ?";

		try (Connection conn = createConnection()) {
			runner.update(conn, insert_sql, detail.getGoodsid(),
					detail.getGoodsname(), detail.getJdprice(),
					detail.getStatus(), detail.getPlace(),
					detail.getDealprice(), detail.getDealuser(),
					detail.getStarttime(), detail.getDealdatetime(),
					detail.getTs());
			runner.update(conn, delete_sql, detail.getGoodsid());
		}
	}
	
	public void deleteWaiting(String goodsid) throws SQLException {
		String delete_sql = "delete from waiting_id where goodsid = ?";

		try (Connection conn = createConnection()) {
			runner.update(conn, delete_sql, goodsid);
		}
	}
}
